#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
--稽核上层数据量
select 1/count(*) from hive.edu_ods.class_studying_student_count;
select 1/count(*) from hive.edu_ods.course_table_upload_detail;
select 1/count(*) from hive.edu_ods.student_leave_apply;
select 1/count(*) from hive.edu_ods.tbh_class_time_table;
select 1/count(*) from hive.edu_ods.tbh_student_signin_record;

delete from hive.edu_dwd.ydh_dim_studying_student where 1=1;
insert into hive.edu_dwd.ydh_dim_studying_student
select id,
       school_id,
       subject_id,
       class_id,
       studying_student_count,
       studying_date
from hive.edu_ods.class_studying_student_count
;



delete from hive.edu_dwd.ydh_dim_course_upload  where 1=1;
insert into hive.edu_dwd.ydh_dim_course_upload
select
    id						-- 'id'
    ,base_id                -- '课程主表id'
    ,class_id               -- '班级id'
    ,class_date             -- '上课日期'
    ,content                -- '课程内容'
    ,teacher_id             -- '老师id'
    ,teacher_name           -- '老师名字'
    ,job_number             -- '工号'
    ,classroom_id           -- '教室id'
    ,classroom_name         -- '教室名称'
    ,is_outline             -- '是否大纲 0 否 1 是'
    ,class_mode             -- '上课模式 0 传统全天 1 ab上午 2 ab下午 3 线上直播'
    ,is_stage_exam          -- '是否阶段考试（0：否 1：是）'
    ,is_pay                 -- '代课费（0：无 1：有）'
    ,tutor_teacher_id       -- '晚自习辅导老师id'
    ,tutor_teacher_name     -- '辅导老师姓名'
    ,tutor_job_number       -- '晚自习辅导老师工号'
    ,is_subsidy             -- '晚自习补贴（0：无 1：有）'
    ,answer_teacher_id      -- '答疑老师id'
    ,answer_teacher_name    -- '答疑老师姓名'
    ,answer_job_number      -- '答疑老师工号'
    ,remark                 -- '备注'
    ,create_time            -- '创建时间'
from hive.edu_ods.course_table_upload_detail
where class_id is not null and cast(class_id as varchar(20))  <> '' and content is not null and content <> '' and content <>'开班典礼'
;


delete from hive.edu_dwd.ydh_fact_student_leave  where 1=1;
insert into hive.edu_dwd.ydh_fact_student_leave
select
     id
    ,class_id         -- '班级id'
    ,student_id       -- '学员id'
    ,audit_state      -- '审核状态 0 待审核 1 通过 2 不通过'
    ,audit_person     -- '审核人'
    ,audit_time       -- '审核时间'
    ,audit_remark     -- '审核备注'
    ,leave_type       -- '请假类型  1 请假 2 销假'
    ,leave_reason     -- '请假原因  1 事假 2 病假'
    ,begin_time       -- '请假开始时间'
    ,begin_time_type  -- '1：上午 2：下午'
    ,end_time         -- '请假结束时间'
    ,end_time_type    -- '1：上午 2：下午'
    ,days             -- '请假/已休天数'
    ,cancel_state     -- '撤销状态  0 未撤销 1 已撤销'
    ,cancel_time      -- '撤销时间'
    ,old_leave_id     -- '原请假id，只有leave_type =2 销假的时候才有'
    ,leave_remark     -- '请假/销假说明'
    ,valid_state      -- '是否有效（0：无效 1：有效）'
    ,create_time      -- '创建时间'
from hive.edu_ods.student_leave_apply
where class_id is not null and cast(class_id as varchar(20)) <>'' and audit_state in (0,1) and leave_type =1 and cancel_state=0 and valid_state =1
;


delete from hive.edu_dwd.ydh_dim_class_time where 1=1 ;
insert into hive.edu_dwd.ydh_dim_class_time
select
    id							-- '主键id',
    ,class_id                   -- '班级id',
    ,morning_template_id        -- '上午出勤模板id'
    ,morning_begin_time         -- '上午开始时间'
    ,morning_end_time           -- '上午结束时间'
    ,afternoon_template_id      -- '下午出勤模板id'
    ,afternoon_begin_time       -- '下午开始时间'
    ,afternoon_end_time         -- '下午结束时间'
    ,evening_template_id        -- '晚上出勤模板id'
    ,evening_begin_time         -- '晚上开始时间'
    ,evening_end_time           -- '晚上结束时间'
    ,use_begin_date             -- '使用开始日期'
    ,use_end_date               -- '使用结束日期'
    ,create_time                -- '创建时间'
    ,create_person              -- '创建人'
    ,remark                     -- '备注'
from hive.edu_ods.tbh_class_time_table
;



delete from hive.edu_dwd.ydh_fact_student_signin where 1=1 ;
insert into hive.edu_dwd.ydh_fact_student_signin
select
    id,
    normal_class_flag,
    time_table_id,
    class_id,
    student_id,
    signin_time,
    signin_date,
    inner_flag,
    signin_type,
    share_state,
    inner_ip
from hive.edu_ods.tbh_student_signin_record
;


"
a=$?
if [ ${a} -eq 0 ];then 
	echo 'Program running status return code:' ${a}
	echo '=============================打完收工！=============================='
else 
	echo 'Program running status return code:' ${a}
	echo '=============================程序出BUG咯！=============================='
	fi